/*
*  This program is free software: you can redistribute it and/or modify
*  it under the terms of the GNU General Public License as published by
*  the Free Software Foundation, either version 3 of the License, or
*  (at your option) any later version.
*
*  This program is distributed in the hope that it will be useful,
*  but WITHOUT ANY WARRANTY; without even the implied warranty of
*  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
*  GNU General Public License for more details.
*
*  You should have received a copy of the GNU General Public License
*  along with this program.  If not, see <http://www.gnu.org/licenses/>.
*/

#define INIT_DB() if(!init_db_connect()) return
#define SQL_REQ_SIZE 512

#define IF_VALID_RESULT(%2)  \
if(dbh == INVALID_HANDLE) \
{ \
	if(IsValidClient(%2)) PrintToChat(%2, "\x05Error while access to database."); \
	return; \
} \
else

#define EXECUTE(%1,%2) SQL_TQuery(dbh, %1, req, %2)

#define EXECUTE_DIRECT(%1,%2,%3) SQL_TQuery(dbh, %1, %3, %2)

/* QUERY( callback, data, string:req, value_to_format) */

#define QUERY_DIRECT(%1,%2,%3) \
INIT_DB(); \
EXECUTE_DIRECT(%1,%2,%3)

#define QUERY(%1,%2,%3,%4) \
INIT_DB(); \
decl String:req[SQL_REQ_SIZE]; \
Format(req, sizeof(req), %3, %4); \
EXECUTE(%1,%2)

#define IF_GET_STEAM_ID(%1) \
decl String:steamid[64]; \
if(!(GetClientAuthString(%1, steamid, sizeof(steamid)))) \
{ \
	PrintToChat(%1, "\x05No Steam logon."); \
} \
else

new Handle:dbh = INVALID_HANDLE;

bool:init_db_connect()
{
	if(dbh == INVALID_HANDLE)
	{
		decl String:error[255]
		dbh = SQL_Connect("revolution", true, error, sizeof(error))
		
		if (dbh == INVALID_HANDLE)
		{
			sub_debug("Could not connect: %s", error)
			return false;
		}
		else /* use utf8 for encoding char */
		{
			/* do a non-threaded query, so, use lock to prevent concurrent access */
			SQL_LockDatabase(dbh);
			SQL_FastQuery(dbh, "SET NAMES \"UTF8\""); 
			SQL_UnlockDatabase(dbh);
		}
	}

	return true;
}


/***************************** ENTITIES ************************************/

stock entities_add_entity_to_db(client, const String:classname[], const String:itemname[], const String:type[], const String:display[], SQLTCallback:callback)
{
	QUERY(callback, client, "INSERT INTO entities(classname, item_name, display_name, ent_type) SELECT '%s', '%s', '%s', id_ent_type FROM entity_types WHERE ent_type_name = '%s';", classname, itemname,display, type);
}

stock entities_get_entity_type(const String:classname[], SQLTCallback:callback)
{
	QUERY(callback, 0, "SELECT ent_type_name FROM entity_types WHERE id_ent_type = (SELECT ent_type FROM entities WHERE classname = '%s');", classname);
	EXECUTE(callback, 0);
}

stock entities_get_all_entity_types(client, SQLTCallback:callback)
{
	QUERY_DIRECT(callback, client, "SELECT ent_type_name FROM entity_types;");
}

stock entities_get_random_primary(SQLTCallback:callback)
{
	QUERY_DIRECT(callback, 0,  "(SELECT item_name, display_name  from entities WHERE ent_type = 2 AND item_name != 'grenade_launcher' GROUP BY item_name ORDER BY RAND() LIMIT 8) UNION ALL (SELECT item_name, display_name FROM entities WHERE item_name = 'grenade_launcher') ORDER BY RAND() LIMIT 10;");
}

stock entities_get_random_secondary(SQLTCallback:callback)
{
	QUERY_DIRECT(callback, 0, "(SELECT item_name, display_name  from entities WHERE ent_type = 3 AND item_name != 'pistol' AND item_name != 'pistol_magnum' GROUP BY item_name ORDER BY RAND() LIMIT 8) UNION ALL (SELECT item_name, display_name FROM entities WHERE item_name = 'pistol_magnum') ORDER BY RAND() LIMIT 10;");
}

/***************************** END ENTITIES ************************************/

/***************************** TRAINING ************************************/

stock training_get_allowed_team(const String:map_name[], SQLTCallback:callback)
{
	QUERY(callback, 0, "SELECT in_game_num FROM teams WHERE id_team = (SELECT allowed_team FROM training_rules WHERE id_map = (SELECT id_map FROM maps WHERE map_name = '%s'));", map_name);
}

stock training_add_rule(client, const String:map_name[], team, time, SQLTCallback:callback)
{
	QUERY( callback, client,"REPLACE INTO training_rules(id_map, allowed_team, time_on_map) SELECT m.id_map, t.id_team, %d FROM maps m, teams t WHERE m.map_name = '%s' AND t.in_game_num = %d;", time, map_name, team);

}

/***************************** END TRAINING ************************************/

/***************************** PLAYERS ************************************/
stock players_register(client)
{
	players_get_id(client, players_do_register);
}

public players_do_register(Handle:owner, Handle:hndl, const String:error[], any:client)
{

	IF_VALID_RESULT(client)
	{
		if(SQL_GetRowCount(hndl) > 0)
		{
			PrintToChat(client, "\x05You are already registered on all \x04Free Arena\x05 servers.");
			return;
		}
	}

	IF_GET_STEAM_ID(client)
	{
		decl String:last_ip[64]; //maybe soon will be used IPv6
		if(GetClientIP(client, last_ip, sizeof(last_ip)))
		{
			decl String:last_name[64];
			if(GetClientName(client, last_name, sizeof(last_name)))
			{
			
				QUERY( players_complete_register, client, "INSERT INTO players(steam_id, last_known_name, last_connect, last_ip, register_date, frozen) VALUES('%s', '%s', NOW(), '%s', NOW(), 0);", steamid, last_name, last_ip);

			}
			else
			{
				PrintToChat(client, "\x05Can't check your name. Aborting.");
			}
		}
		else
		{
			PrintToChat(client, "\x05Can't check your IP address. Aborting.");
		}
	}

}

public players_complete_register(Handle:owner, Handle:hndl, const String:error[], any:client)
{
	IF_VALID_RESULT(client)
	{
		PrintToChat(client, "\x05Welcome in the \x04Free Arena\x05 database!");
	}
}

stock players_unregister(client)
{
	players_get_id(client, players_do_unregister);
}

public players_do_unregister(Handle:owner, Handle:hndl, const String:error[], any:client)
{
	IF_VALID_RESULT(client)
	{
		if(SQL_FetchRow(hndl))
		{
			new id = SQL_FetchInt(hndl,0);
		
			QUERY(players_complete_unregister, client, "DELETE FROM players WHERE id_player = %d", id);

		}
		else
		{
			PrintToChat(client, "\x05You are not registered on \x04Free Arena\x05 servers.");
			return;
		}
	}

}

public players_complete_unregister(Handle:owner, Handle:hndl, const String:error[], any:client)
{
	IF_VALID_RESULT(client)
	{
		PrintToChat(client, "\x05You successfully removed from the \x04Free Arena\x05 database!");
	}	
}


stock players_freeze_account(client)
{
	players_get_id(client, players_do_freeze_account);
}

public players_do_freeze_account(Handle:owner, Handle:hndl, const String:error[], any:client)
{
	IF_VALID_RESULT(client)
	{
		if(SQL_FetchRow(hndl))
		{
			new id = SQL_FetchInt(hndl,0);
			QUERY(players_complete_freeze_account, client,  "UPDATE players SET frozen = 1 WHERE id_player = %d", id);
		}
		else
		{
			PrintToChat(client, "\x05You are not registered on \x04Free Arena\x05 servers.");
			return;
		}
	}

}

public players_complete_freeze_account(Handle:owner, Handle:hndl, const String:error[], any:client)
{
	IF_VALID_RESULT(client)
	{
		PrintToChat(client, "\x05You successfully frozen your account from the \x04Free Arena\x05 database!");
	}	
}

stock players_unfreeze_account(client)
{
	players_get_id(client, players_do_unfreeze_account);
}

public players_do_unfreeze_account(Handle:owner, Handle:hndl, const String:error[], any:client)
{
	IF_VALID_RESULT(client)
	{
		if(SQL_FetchRow(hndl))
		{
			new id = SQL_FetchInt(hndl,0);
			QUERY(players_complete_unfreeze_account, client,  "UPDATE players SET frozen = 0 WHERE id_player = %d", id);
		}
		else
		{
			PrintToChat(client, "\x05You are not registered on \x04Free Arena\x05 servers.");
			return;
		}
	}

}

public players_complete_unfreeze_account(Handle:owner, Handle:hndl, const String:error[], any:client)
{
	IF_VALID_RESULT(client)
	{
		PrintToChat(client, "\x05You successfully unfreeze your account from the \x04Free Arena\x05 database!");
	}	
}

stock players_update_database(client, server_id)
{
	IF_GET_STEAM_ID(client)
	{
		decl String:last_ip[64]; //maybe soon will be used IPv6
		if(GetClientIP(client, last_ip, sizeof(last_ip)))
		{
			decl String:last_name[64];
			if(GetClientName(client, last_name, sizeof(last_name)))
			{
				QUERY(players_complete_update, client,  "UPDATE players SET last_known_name = '%s', last_connect = NOW(), last_ip = '%s', last_serv = %d WHERE steam_id = '%s' AND frozen = 0;", last_name, last_ip, server_id, steamid);
			}
		}
	}		
}

public players_complete_update(Handle:owner, Handle:hndl, const String:error[], any:client)
{
	sub_debug("updated %N", client);
}


stock players_get_id(client, SQLTCallback:callback)
{
	IF_GET_STEAM_ID(client)
	{		
		QUERY(callback, client, "SELECT id_player FROM players WHERE steam_id = '%s';", steamid);
	}

}

/***************************** END PLAYERS ************************************/

/***************************** MAP ROTATION ************************************/

/* empty result set if last map */
stock maps_get_map_info(const String:current_map[], SQLTCallback:callback)
{
	QUERY(callback, 0, "SELECT m.map_name, t.time_on_map FROM maps m, training_rules t WHERE m.id_map = (SELECT id_map FROM maps WHERE map_name = '%s') AND t.id_map = m.id_map;", current_map);
}

stock maps_get_next_map(const String:current_map[], SQLTCallback:callback)
{
	QUERY(callback, 0, "SELECT m.map_name, t.time_on_map FROM rocket_jump_maps m, training_rules t WHERE m.id_map = (SELECT id_map +1 FROM maps WHERE map_name = '%s') AND t.id_map = m.id_map;", current_map);
}

/***************************** END MAP ROTATION ************************************/

/***************************** OBJECTS ************************************/

stock objects_add_object(client, const String:current_map[], const String:type[], id_serv, const String:entity[], const Float:pos[3], const Float:angle[3], SQLTCallback:callback)
{
	QUERY(callback, client, "REPLACE INTO objects SELECT m.id_map, ot.id_obj_type, %d, e.id_entity, %f, %f, %f, %f, %f, %f FROM maps m, object_types ot, entities e WHERE m.map_name = '%s' AND ot.obj_type_name = '%s' AND e.classname = '%s';", id_serv, pos[0], pos[1], pos[2], angle[0], angle[1], angle[2], current_map, type, entity);

}

stock objects_get_objects(const String:current_map[], id_serv, SQLTCallback:callback)
{
	QUERY(callback, 0, "SELECT e.classname, m.model_path, o.coord_x, o.coord_y, o.coord_z, o.angle_x, o.angle_y, o.angle_z, ot.obj_type_collision_group, ot.obj_type_solid, ot.obj_type_solid_flags, ot.obj_type_solid_type, ot.obj_type_move_type, ot.obj_type_move_collide, ot.obj_type_name FROM entities e, models m, objects o, object_types ot WHERE o.id_map = (SELECT id_map from maps where map_name = '%s') AND ot.id_obj_type = o.id_obj_type AND o.id_server = %d AND e.id_entity = o.id_entity AND m.id_model = ot.id_model;", current_map, id_serv);
}

/***************************** END OBJECTS ************************************/


/***************************** MAPS CHOOSING ************************************/

stock maps_choosing_get_map_info(const String:current_map[], SQLTCallback:callback)
{
	QUERY(callback, 0, "SELECT map_type, id_campaign from maps WHERE map_name = '%s'", current_map);
}

stock maps_choosing_update_campaign(id_campaign, id_server)
{
	QUERY(maps_choosing_done_update, 0, "UPDATE campaigns_tracking SET num_played = num_played + 1, last_played = NOW() WHERE id_server = %d AND id_campaign = %d", id_server, id_campaign);
}

public maps_choosing_done_update(Handle:owner, Handle:hndl, const String:error[], any:junk)
{
	sub_debug("Maps updated");
}

stock maps_choosing_select_random(id_server, SQLTCallback:callback)
{
	/* filter the 3rd last played map and the older played map */
	QUERY(callback, 0, "SELECT c.camp_name, m.map_name, c.id_campaign, c.camp_type FROM maps m, campaigns_tracking t, campaigns c WHERE t.id_server = %d AND m.id_campaign = t.id_campaign AND m.map_type = 1 AND c.id_campaign = t.id_campaign AND t.last_played > (SELECT last_played FROM  campaigns_tracking ORDER BY last_played ASC LIMIT 1) AND t.last_played < (SELECT last_played FROM (SELECT last_played FROM campaigns_tracking ORDER BY last_played DESC LIMIT 3) ct ORDER BY last_played ASC LIMIT 1) ORDER BY RAND() LIMIT 1;", id_server);
}

stock maps_choosing_select_older(id_server, id_rand_campaign, SQLTCallback:callback)
{
	QUERY(callback, id_rand_campaign, "SELECT c.camp_name, m.map_name, c.id_campaign, c.camp_type FROM maps m, campaigns_tracking t, campaigns c WHERE t.id_server = %d AND t.last_played = (SELECT MIN(last_played) FROM campaigns_tracking) AND m.id_campaign = t.id_campaign AND m.map_type = 1 AND c.id_campaign = t.id_campaign LIMIT 1;", id_server);
}

stock maps_choosing_select_less_played(id_server, without_last, without_random, SQLTCallback:callback)
{
	QUERY(callback, 0, "SELECT c.camp_name, m.map_name, c.camp_type FROM maps m, campaigns_tracking t, campaigns c WHERE t.id_server = %d AND t.num_played = (SELECT MIN(num_played) FROM campaigns_tracking) AND m.id_campaign = t.id_campaign AND m.map_type = 1 AND c.id_campaign = t.id_campaign AND t.id_campaign != %d AND t.id_campaign != %d ORDER BY RAND() LIMIT 1;", id_server, without_random, without_last);
 
}

stock maps_choosing_select_less_played_all_case(id_server, SQLTCallback:callback)
{
	QUERY(callback, 0, "SELECT m.map_name FROM maps m, campaigns_tracking t, campaigns c WHERE t.id_server = %d AND t.num_played = (SELECT MIN(num_played) FROM campaigns_tracking) AND m.id_campaign = t.id_campaign AND m.map_type = 1 AND c.id_campaign = t.id_campaign ORDER BY RAND() LIMIT 1;", id_server);
 
}


/***************************** END MAPS CHOOSING ************************************/
